package com.tiza.test.db;

import org.apache.commons.lang3.RandomStringUtils;
import org.apache.commons.lang3.RandomUtils;
import org.apache.commons.math3.stat.descriptive.DescriptiveStatistics;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *  MySQL Problem DNS lead to 'unauthenticated  user'
 */
public class MysqlDNSUnauthUser {

    public static final Logger LOG = LoggerFactory
            .getLogger(MysqlDNSUnauthUser.class);

    public static void preparedData() {
        Connection conn = null;
        PreparedStatement stmt = null;

        String sql = "insert into person(name, sex, age) values(?, ?, ?)";
        try {
            conn = DBUtils.getConnection();
            conn.setAutoCommit(false);
            stmt = conn.prepareStatement(sql);

            for (int i = 0; i < 1000; i++) {
                String name = RandomStringUtils.randomAlphabetic(10);
                int sex = RandomUtils.nextInt(0, 2);
                int age = RandomUtils.nextInt(1, 100);
                stmt.setString(1, name);
                stmt.setInt(2, sex);
                stmt.setInt(3, age);
                stmt.addBatch();
            }

            stmt.executeBatch();
            conn.commit();

        } catch (SQLException e) {
            LOG.error(e.getMessage(), e);
        } finally {
            DBUtils.close(conn, stmt);
        }
    }


    /**
     * 执行查询操作
     * @return
     */
    public static long query() {

        long start = System.currentTimeMillis();
        Connection conn = null;
        Statement stmt = null;

        String sql = "select 1";
        try {
            // 创建新的连接
            conn = DBUtils.getConnection();
            stmt = conn.createStatement();
            stmt.executeQuery(sql);

        } catch (SQLException e) {
            LOG.error(e.getMessage(), e);
        } finally {
            DBUtils.close(conn, stmt);
        }

        long end = System.currentTimeMillis();
        return end - start;
    }

    public static void main(String[] args) {

        // 描述统计
        DescriptiveStatistics stats = new DescriptiveStatistics();
        int n = 0;
        while(true) {
            n++;
            long time = query();
            stats.addValue(time);
            if(n == 1000) break;
        }

        LOG.info(stats.toString());
    }
}
